Go to the documentation of this file.
00001 using System;
00002 using System.Collections.Generic;
00003 using System.ComponentModel;
00004 using System.Data;
00005 using System.Data.OleDb;
00006 using System.Data.SqlClient;
00007 using System.Diagnostics;
00008 using System.Drawing;
00009 using System.IO;
00010 using System.Linq;
00011 using System.Text;
00012 using System.Windows.Forms;
00013 using System.Xml;
00014 using System.Threading; 
00015 using FillCustomQueryTableCL;
00016 
00017 namespace RAISInstall
00018 {
00019     public partial class MainForm : Form
00020     {
00021         private string accessConnection;
00022         private string sqlConnection;
00023         
00024         delegate void UpdateStatusDelegate(string text);
00025 
00026         public MainForm()
00027         {
00028             InitializeComponent();
00029 
00030                         // Set initial values
00031                         sqlServerTextBox.Text = @"localhost\SQLEXPRESS";
00032                         sqlCatalogTextBox.Text = "RAIS";
00033         }
00034 
00035         private void button1_Click(object sender, EventArgs e)
00036         {
00037             if (mdbFile.ShowDialog() == DialogResult.OK)
00038             {
00039                 mdbFileName.Text = mdbFile.FileName;
00040                 creatorPassTextbox.Enabled = true;
00041                 creatorLoginTextbox.Enabled = true;
00042             }
00043         }
00044 
00045         private void button2_Click(object sender, EventArgs e)
00046         {
00047             if (mdwFile.ShowDialog() == DialogResult.OK)
00048                 mdwFileName.Text = mdwFile.FileName;
00049         }
00050 
00051         private void button3_Click(object sender, EventArgs e)
00052         {
00053                         tbStatus.Clear();
00054 
00055             string errorString = "";
00056             if ((passTextBox.Text == "") && (!cbIntegratedSecurity.Checked))
00057                 errorString = "Enter User password";
00058             if ((loginTextBox.Text == "") && (!cbIntegratedSecurity.Checked))
00059                 errorString = "Enter User login";
00060             if (sqlCatalogTextBox.Text == "")
00061                 errorString = "Enter SQL Server catalog";
00062             if (sqlServerTextBox.Text == "")
00063                 errorString = "Enter SQL Server name";
00064             if (mdwFileName.Text == "")
00065                 errorString = "Select MDW file";
00066             if (mdbFileName.Text == "")
00067                 errorString = "Select RAIS Creator MDB file";
00068 
00069                         if (errorString == "")
00070             {
00071                 //string accessConnectionFormat = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID=administrator;Password=;Jet OLEDB:System Database={1};";
00072                 string accessConnectionFormat = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID={2};Password={3};Jet OLEDB:System Database={1};";
00073                 string sqlConnectionFormat = "Data Source={0};Initial Catalog={1};Integrated Security=True;";
00074                 //string sqlConnectionFormat;
00075                 //if (cbIntegratedSecurity.Checked)
00076                 //{
00077                     //sqlConnectionFormat = "Data Source={0};Initial Catalog={1};Integrated Security=True;";
00078                     sqlConnection = string.Format(sqlConnectionFormat, sqlServerTextBox.Text, sqlCatalogTextBox.Text);
00079                 //}
00080                 //else
00081                 //{
00082                 //    sqlConnectionFormat = "Data Source={0};Initial Catalog={1};Integrated Security=False;User ID={2};Password={3};";
00083                 //    sqlConnection = string.Format(sqlConnectionFormat, sqlServerTextBox.Text, sqlCatalogTextBox.Text, loginTextBox.Text, passTextBox.Text);
00084                 //}
00085                 accessConnection = string.Format(accessConnectionFormat, mdbFileName.Text, mdwFileName.Text, creatorLoginTextbox.Text, creatorPassTextbox.Text);
00086                 tbStatus.Focus();
00087                 Thread migrationProcess = new Thread(new ThreadStart(this.MigrationProcess));
00088                 migrationProcess.Start();
00089                 tbStatus.Focus();
00090             }
00091             else
00092             tbStatus.AppendText(errorString);
00093                 }
00094 
00095         private void UpdateStatusBox(string text)
00096         {
00097             tbStatus.AppendText(text);
00098             tbStatus.Refresh();
00099         }
00100 
00101         private void UpdateStatus(string text)
00102         {
00103             this.Invoke(new UpdateStatusDelegate(UpdateStatusBox), new object[] { text });
00104         }
00105 
00106         private void MigrationProcess()
00107         {
00108             string errorString = "";
00109             try
00110             {
00111                 UpdateStatus("Migration process started...");
00112                 using (Table table = new Table(accessConnection, sqlConnection))
00113                 {
00114                     UpdateStatus("\r\nCopy [RAIS Table Group], [RAIS Table], [RAIS Field] tables from RAIS Creator...");
00115                     table.Copy("RAIS Table", "RAIS_Table");
00116                     table.Copy("RAIS Table Group", "RAIS_Table_Group");
00117                     table.Copy("RAIS Field", "RAIS_Field");
00118                 }
00119 
00120                 UpdateStatus("\r\nTables have been copied.");
00121 
00122                 UpdateStatus("\r\nNew tables are being created...");
00123                 ExecProcess(Application.StartupPath, "CreateDBObjects_v1.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00124                 UpdateStatus("\r\nNew tables have been created.");
00125 
00126                 UpdateStatus("\r\nFrom Access migrated queries are being created...");
00127                 ExecProcess(Application.StartupPath, "CreateMigratedFromAccessQueries.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00128                 UpdateStatus("\r\nFrom Access migrated queries have been created.");
00129 
00130                 UpdateStatus("\r\nNew DB objects are being created...");
00131                 ExecProcess(Application.StartupPath, "CreateDBObjectsForMigration.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00132                 UpdateStatus("\r\nNew DB objects have been created.");
00133 
00134                 UpdateStatus("\r\nRADEV tables are being created...");
00135                 ExecProcess(Application.StartupPath, "CreateRADEVObjects.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00136                 UpdateStatus("\r\nRADEV tables have been created.");
00137 
00138                 UpdateStatus("\r\nCustomized objects are being imported...");
00139                 ExecProcess(Application.StartupPath, "ImportCustomizedObjects.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00140                 UpdateStatus("\r\nCustomized objects have been imported.");
00141 
00142                 UpdateStatus("\r\nCustom query table is being filled...");
00143                 string queriesFileName = Application.StartupPath + @"\Create Scripts\Queries.xml";
00144                 QueryTable.FillQueryTable(sqlConnection, queriesFileName);
00145                 UpdateStatus("\r\nCustom query table has been filled.");
00146 
00147                 UpdateStatus("\r\nUpdating Table [RAIS Table] - Adding Radiation Events...");
00148                 ExecProcess(Application.StartupPath, "UpdateRaisTable.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00149                 UpdateStatus("\r\nUpdating Table [RAIS Table] completed.");
00150 
00151                 UpdateStatus("\r\nUpdating to RAIS 3.2 DB ...");
00152                 ExecProcess(Application.StartupPath, "UpdateRais31ToRais32.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00153                 ExecProcess(Application.StartupPath, "Rais32LanguageIntegration.cmd", sqlServerTextBox.Text + " " + sqlCatalogTextBox.Text);
00154                 InstallHelpLanguages();
00155                 UpdateStatus("\r\nUpdating to RAIS 3.2 DB finished");
00156 
00157                 UpdateStatus("\r\nQuery table for Rais 3.2 is being filled...");
00158                 queriesFileName = Application.StartupPath + @"\RAIS32 Update Scripts\Rais32Queries.xml";
00159                 QueryTable.FillQueryTable(sqlConnection, queriesFileName);
00160                 UpdateStatus("\r\nQuery table for Rais 3.2 has been filled.");
00161 
00162                 UpdateStatus("\r\n\r\nMigration has been finished.");
00163 
00164                 ReCreateUser();
00165             }
00166             catch (Exception ex)
00167             {
00168                 errorString = ex.Message;
00169                 //UpdateStatusBox(errorString);
00170                 //UpdateStatusBox("Migrated process finishing with error");
00171                 if (ex is SqlException)
00172                 {
00173                     UpdateStatus("\r\nConnection to MS SQL Server failed...");
00174                 }
00175                 else if (ex is OleDbException)
00176                 {
00177                     UpdateStatus("\r\nConnection to ACCESS database failed...");
00178                 }
00179                 UpdateStatus("\r\n"+errorString);
00180                 //UpdateStatus("\r\nMigrated process finishing with error");
00181                 UpdateStatus("\r\nMigration process aborted with error");
00182             }
00183         }
00184 
00185         private void ReCreateUser()
00186         {
00187             using (var connection = new SqlConnection(sqlConnection))
00188             {
00189                 connection.Open();
00190 
00191                 string commandText = string.Format(" IF NOT EXISTS(select * from sys.syslogins where name = '{0}') " +
00192                        "        CREATE LOGIN {0} WITH PASSWORD='{1}', DEFAULT_DATABASE = {2}, CHECK_POLICY=OFF", loginTextBox.Text,
00193                                    passTextBox.Text,
00194                                    sqlCatalogTextBox.Text);
00195 
00196                 SqlCommand command = new SqlCommand(commandText, connection);
00197                 command.ExecuteNonQuery();
00198 
00199                 try
00200                 {
00201                     commandText = string.Format("drop user {0}", loginTextBox.Text);
00202 
00203                     command = new SqlCommand(commandText, connection);
00204                     command.ExecuteNonQuery();
00205                 }
00206                 catch (Exception) { }
00207 
00208                 commandText = string.Format(
00209                             " CREATE USER [{0}] FOR LOGIN [{0}] " +
00210                             " EXEC sp_addrolemember N'db_datareader', N'{0}' " +
00211                             " EXEC sp_addrolemember N'db_datawriter', N'{0}' " +
00212                             " EXEC sp_addrolemember N'db_owner', N'{0}' "
00213                             , loginTextBox.Text);
00214 
00215                 command = new SqlCommand(commandText, connection);
00216                 command.ExecuteNonQuery();
00217             }
00218         }
00219 
00220                 private static int ExecProcess(string strWorkingDirectory, string strExePath, string strArguments)
00221                 {
00222                         Process prcStsadmin = new Process();
00223                         prcStsadmin.StartInfo.FileName = strExePath;
00224                         prcStsadmin.StartInfo.WorkingDirectory = strWorkingDirectory;
00225                         prcStsadmin.StartInfo.UseShellExecute = true;
00226                         prcStsadmin.StartInfo.RedirectStandardOutput = false;
00227                         prcStsadmin.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
00228                         prcStsadmin.StartInfo.Arguments = strArguments;
00229                         prcStsadmin.StartInfo.CreateNoWindow = false;
00230 
00231                         prcStsadmin.Start();
00232                         prcStsadmin.WaitForExit();
00233                         return prcStsadmin.ExitCode;
00234                 }
00235 
00236         private void cbIntegratedSecurity_CheckedChanged(object sender, EventArgs e)
00237         {
00238             loginTextBox.Enabled = !cbIntegratedSecurity.Checked;
00239             passTextBox.Enabled = !cbIntegratedSecurity.Checked;
00240             label5.Enabled = !cbIntegratedSecurity.Checked;
00241             label6.Enabled = !cbIntegratedSecurity.Checked;
00242         }
00243 
00244 
00245         private void InstallHelpLanguages()
00246         {
00247 
00248             string connectionString1 = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", sqlServerTextBox.Text, sqlCatalogTextBox.Text);
00249             
00250             using (SqlConnection connection = new SqlConnection(connectionString1))
00251             {
00252                 SqlCommand com = new SqlCommand("", connection);
00253                 SqlDataAdapter da = new SqlDataAdapter();
00254                 System.Data.DataTable dtHelpTemp = new System.Data.DataTable();
00255 
00256                 connection.Open();
00257 
00258                 com.CommandText = "SELECT * FROM [Help Temp]";
00259 
00260                 da.SelectCommand = com;
00261                 da.Fill(dtHelpTemp);
00262                 
00263                 com.CommandText = "DELETE FROM [Help] " + 
00264                                   "DBCC CHECKIDENT ('[Help]',RESEED,0) " +
00265                                   "SET IDENTITY_INSERT [Help] ON ";
00266                 com.ExecuteNonQuery();
00267                 
00268                 
00269                 com.Parameters.Add("@HelpText", System.Data.SqlDbType.NVarChar).Value = "";
00270                 com.Parameters.Add("@FKSupportedLanguageID", System.Data.SqlDbType.Int).Value = 0;
00271                 com.Parameters.Add("@PKHelpID", System.Data.SqlDbType.Int).Value = 0;
00272                 com.Parameters.Add("@RAIS_TIME_STAMP", System.Data.SqlDbType.Date).Value = null;
00273 
00274                 com.Parameters.Add("@DynamicMaskName", System.Data.SqlDbType.NVarChar).Value = "";
00275                 com.Parameters.Add("@MaskName", System.Data.SqlDbType.NVarChar).Value = "";
00276                 com.Parameters.Add("@CategoryName", System.Data.SqlDbType.NVarChar).Value = "";
00277                 com.Parameters.Add("@SubcategoryName", System.Data.SqlDbType.NVarChar).Value = "";
00278 
00279 
00280                 string CommandText1 =
00281                 "INSERT INTO HELP( " +
00282                         "[FK Category ID],[FK Subcategory ID],[FK Mask ID],[FK Dynamic Mask ID],[FK Supported Language ID],[Help Text],RAIS_TIME_STAMP,[PK Help ID]) " +
00283                     "SELECT [PK Category ID], [PK Subcategory ID], [PK Mask ID], Null, @FKSupportedLanguageID,@HelpText,@RAIS_TIME_STAMP,@PKHelpID " +
00284                     "FROM Category " +
00285                           "LEFT JOIN Subcategory ON [FK Category ID] = [PK Category ID] " +
00286                           "LEFT JOIN Mask ON [FK Subcategory ID] = [PK Subcategory ID] " +
00287                     "WHERE " +
00288                           "[Mask Name]=@MaskName AND " +
00289                           "[Category Name]=@CategoryName AND " +
00290                           "[Subcategory Name]=@SubcategoryName ";
00291 
00292 
00293                 string CommandText2 = 
00294                 "INSERT INTO HELP( " +
00295                         "[FK Category ID],[FK Subcategory ID],[FK Mask ID],[FK Dynamic Mask ID],[FK Supported Language ID],[Help Text],RAIS_TIME_STAMP,[PK Help ID]) " +
00296                     "SELECT [PK Category ID], [PK Subcategory ID], [PK Mask ID], [PK Dynamic Mask ID], @FKSupportedLanguageID,@HelpText,@RAIS_TIME_STAMP,@PKHelpID " +
00297                     "FROM Category " +
00298                           "LEFT JOIN Subcategory ON [FK Category ID] = [PK Category ID] " +
00299                           "LEFT JOIN Mask ON [FK Subcategory ID] = [PK Subcategory ID] " +
00300                           "LEFT JOIN [Dynamic Mask] ON [FK Mask ID] = [PK Mask ID] " +
00301                     "WHERE " +
00302                           "[Dynamic Mask Name]=@DynamicMaskName AND " +  
00303                           "[Mask Name]=@MaskName AND " +
00304                           "[Category Name]=@CategoryName AND " +
00305                           "[Subcategory Name]=@SubcategoryName ";
00306 
00307 
00308                 foreach (System.Data.DataRow row in dtHelpTemp.Rows)
00309                 {
00310                     if (Convert.IsDBNull(row["Dynamic Mask Name"]))
00311                         com.CommandText = CommandText1;
00312                     else
00313                         com.CommandText = CommandText2;
00314                     
00315                     com.Parameters["@HelpText"].Value = row["Help Text"];
00316                     com.Parameters["@FKSupportedLanguageID"].Value = row["FK Supported Language ID"];
00317                     com.Parameters["@PKHelpID"].Value = row["PK Help ID"];
00318                     com.Parameters["@RAIS_TIME_STAMP"].Value = row["RAIS_TIME_STAMP"];
00319                     
00320                     com.Parameters["@DynamicMaskName"].Value = row["Dynamic Mask Name"];
00321                     com.Parameters["@MaskName"].Value = row["Mask Name"];
00322                     com.Parameters["@CategoryName"].Value = row["Category Name"];
00323                     com.Parameters["@SubcategoryName"].Value = row["Subcategory Name"];
00324                         
00325                     com.ExecuteNonQuery();
00326                 }
00327 
00328                 com.CommandText = "SET IDENTITY_INSERT [Help] OFF " +
00329                                   "DROP TABLE [Help Temp]";
00330                 com.ExecuteNonQuery();
00331                 
00332                 connection.Close();
00333 
00334             }
00335         }
00336     }
00337  }